#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-19
"""
The tmpo_drivers table holds all the basic data as it relates to 
drivers. This API manages all access to or modification of that table.

    +----------------------+-----------------+--------------------------+
    |     Column Name      |       Type      |       Description        |
    +----------------------+-----------------+--------------------------+
    | driver_id            | int,pk,identity | Unique record identifier |
    | name                 | varchar         | driver name              |
    | telephone	           | varchar         | telephone number         |
    | address              | varchar         | address                  |
    | license_number       | varchar         | driver's license number  |
    | license_expiration   | datetime        | license expiration date  |
    | license_class        | varchar         | license class            |
    | license_endorsements | varchar         | license endorsements     |
    | license_restrictions | varchar         | license restrictions     |
    | active               | bit             | logical delete flag      |
    +----------------------+-----------------+--------------------------+

Information from this table is referenced by some of the other APIs, through
the driver_id key.

The functions found in this API are:

 get_all_drivers 
   selects all information, for all drivers out of the database, no filter

 get_driver_data 
   selects all information for one driver out of the database, using the
   driver_id as the filter

 add_driver
   adds a driver to the database, using a form to read all the required
   information from.

 update_driver
   modifies a current record in the database, using the driver_id value
   as the filter, and reading a form for all the values (including any
   that were not changed)

 delete_driver
   flags a driver as 'inactive' in the database, uses the driver_id value
   as the filter. This is a logical delete, and not a physical one - such
   that the criteria for 'deleted' is that active has a 0 value, and 'active'
   has a '1' value.

 get_available_drivers
   queries tmpo_driver_unavailable, using the driver_id as the filter
   as well as checking against a time/date range for availability verification.
"""

from string import Template
from tack.db import query, sql_string, sql_int, sql_date
from tack.lib import string_app, content_headers, status_codes, json_app
from tack.lib.dicttools import query_to_list
import time

get_drivers = """
SELECT
    *
FROM
    tmpo_driver
WHERE
    active = 1
ORDER BY name
"""

get_one_driver = """
SELECT 
    *
FROM
    tmpo_driver
WHERE
    active = 1 
    AND driver_id = $driver_id
"""

check_for_driver = """
SELECT 
    driver_id
FROM
    tmpo_driver
WHERE
    active = 1
    AND name = $name
"""

driver_add = """
INSERT INTO
    tmpo_driver(
        name,
        telephone,
        address,
        license_number,
        license_expiration,
        license_class,
        license_endorsements,
        license_restrictions)
VALUES(
    $name,
    $telephone,
    $address,
    $license_number,
    $license_expiration,
    $license_class,
    $license_endorsements,
    $license_restrictions)
"""

driver_update = """
UPDATE
   tmpo_driver
SET
    name=$name,
    telephone=$telephone,
    address=$address,
    license_number=$license_number,
    license_expiration=$license_expiration,
    license_class=$license_class,
    license_endorsements=$license_endorsements,
    license_restrictions=$license_restrictions
WHERE
    driver_id = $driver_id
"""

driver_delete = """
UPDATE
    tmpo_driver
SET
    active = 0
WHERE
    driver_id = $driver_id
"""

driver_availability = """
SELECT
    *    
FROM
    tmpo_driver
WHERE
    active = 1 
    AND (driver_id NOT IN( 
      SELECT
        driver_id
      FROM
        tmpo_driver_unavailable 
      WHERE (du_start <= '$start' AND du_end >= '$finish') 
         OR (du_start >= '$start' AND du_start <= '$finish') 
         OR (du_end >= '$start' AND du_end <= '$finish') 
      )
    AND driver_id NOT IN 
      ( SELECT 
          driver_id
        FROM
          tmpo_schedule_resourced 
        WHERE
          (driver_id IS NOT NULL 
            AND start <= '$start' 
            AND finish >= '$finish' 
            AND id != $record_id 
            AND (parent_id != $record_id OR parent_id IS NULL)) 
          OR (driver_id IS NOT NULL 
            AND start >= '$start' 
            AND start <= '$finish' 
            AND id != $record_id 
            AND (parent_id != $record_id OR parent_id IS NULL)) 
          OR (driver_id IS NOT NULL 
            AND finish >= '$start' 
            AND finish <= '$finish' 
            AND id != $record_id 
            AND (parent_id != $record_id OR parent_id IS NULL)) 
)) 
"""

def get_all_drivers(environ, start_response):
    sql = get_drivers
    rows = query(environ['connection'], sql)
    output = {}
    labels = ['id','name','telephone','address','license_number',
              'license_expiration','license_class','license_endorsements',
              'license_restrictions'] 
    output['drivers'] = query_to_list(rows, labels)
    ##output = json_dumps(output, indent=2)
    return json_app(output)(environ, start_response)

def get_driver_data(environ, start_response):
    driver_id = environ['dispatcher.groups']['driver_id']
    sql = Template(get_one_driver).substitute(locals())
    rows = query(environ['connection'], sql)
    output = {}
    labels = ['id','name','telephone','address','license_number',
              'license_expiration','license_class','license_endorsements',
              'license_restrictions'] 
    output['drivers'] = query_to_list(rows, labels)
    return json_app(output)(environ, start_response)

def add_driver(environ, start_response):
    form = environ['form']
    name = sql_string(form['name'])
    telephone = sql_string(form['telephone'])
    address = sql_string(form['address'])
    license_number = sql_string(form['license_number'])
    license_expiration = sql_date(form['license_expiration'], '%Y-%m-%d')
    license_class = sql_string(form['license_class'])
    license_endorsements = sql_string(form['license_endorsements'])
    license_restrictions = sql_string(form['license_restrictions'])
    sql = Template(check_for_driver).substitute(locals())
    rows = query(environ['connection'], sql)
    if len(rows):
        start_response(status_codes.conflict, content_headers.default_plain())
        return ['Driver Exists']
    sql = Template(driver_add).substitute(locals())
    query(environ['connection'], sql)
    rows = query(environ['connection'], 'SELECT CAST(@@IDENTITY AS INT)')
    return json_app(rows)(environ, start_response)

def update_driver(environ, start_response):
    driver_id = environ['dispatcher.groups']['driver_id']
    form = environ['form']
    name = sql_string(form['name'])
    telephone = sql_string(form['telephone'])
    address = sql_string(form['address'])
    license_number = sql_string(form['license_number'])
    license_expiration = sql_date(form['license_expiration'], '%Y-%m-%d %H:%M')
    throwaway = time.strptime(license_expiration, "'%Y-%m-%d %H:%M'")
    license_class = sql_string(form['license_class'])
    license_endorsements = sql_string(form['license_endorsements'])
    license_restrictions = sql_string(form['license_restrictions'])
    sql = Template(driver_update).substitute(locals())
    rows = query(environ['connection'], sql)
    output = 'Driver Updated'
    return string_app(output)(environ, start_response)

def delete_driver(environ, start_response):
    driver_id = environ['dispatcher.groups']['driver_id']
    sql = Template(driver_delete).substitute(locals())
    rows = query(environ['connection'], sql)
    output = 'Driver Deleted'
    return string_app(output)(environ, start_response)

def get_available_drivers(environ, start_response):
    try:
        start = environ['dispatcher.groups']['start']
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = environ['dispatcher.groups']['finish'], '%Y-%m-%d %H:%M'
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        try:
            record_id = environ['dispatcher.groups']['record_id']
        except:
            record_id = -1
        sql = Template(driver_availability).substitute(locals())
        rows = query(environ['connection'], sql)
        output = {}
        labels = ['id','name','telephone','address','license_number',
                  'license_expiration','license_class','license_endorsements',
                  'license_restrictions'] 
        output['drivers'] = query_to_list(rows, labels)
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(output)(environ, start_response)
